#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class StudentProvider : CY.GFive.Core.Providers.DALProvider.IStudentProvider
	{
		#region Sql
		private static readonly string SqlSelectStudent = "SELECT [Id], [Code],[StdName],[UsedName],[StdState],[IndentifyType],[StdCate],[IndentifyCode],[Gender],[AdmissDate],[ProfessCode],[ResidenceName],[ResidenceType],[Nation],[Birthday],[AdmissTypeCode],[Phone],[CorSchlCode],[CorTypeCode],[Zip_Code],[RegistedCode],[PoliticalCode],[Origin],[OverSea],[GrdSchool],[StdAddress],[Email],[Des],[RegisteDate] FROM [Student] WHERE  [Id]=@Id";

		private static readonly string SqlInsertStudent = "INSERT INTO [Student]([Code],[StdName],[UsedName],[StdState],[IndentifyType],[StdCate],[IndentifyCode],[Gender],[AdmissDate],[ProfessCode],[ResidenceName],[ResidenceType],[Nation],[Birthday],[AdmissTypeCode],[Phone],[CorSchlCode],[CorTypeCode],[Zip_Code],[RegistedCode],[PoliticalCode],[Origin],[OverSea],[GrdSchool],[StdAddress],[Email],[Des],[RegisteDate]) values(@Code,@StdName,@UsedName,@StdState,@IndentifyType,@StdCate,@IndentifyCode,@Gender,@AdmissDate,@ProfessCode,@ResidenceName,@ResidenceType,@Nation,@Birthday,@AdmissTypeCode,@Phone,@CorSchlCode,@CorTypeCode,@Zip_Code,@RegistedCode,@PoliticalCode,@Origin,@OverSea,@GrdSchool,@StdAddress,@Email,@Des,@RegisteDate);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateStudent = "UPDATE [Student] SET [Code] = @Code,[StdName] = @StdName,[UsedName] = @UsedName,[StdState] = @StdState,[IndentifyType] = @IndentifyType,[StdCate] = @StdCate,[IndentifyCode] = @IndentifyCode,[Gender] = @Gender,[AdmissDate] = @AdmissDate,[ProfessCode] = @ProfessCode,[ResidenceName] = @ResidenceName,[ResidenceType] = @ResidenceType,[Nation] = @Nation,[Birthday] = @Birthday,[AdmissTypeCode] = @AdmissTypeCode,[Phone] = @Phone,[CorSchlCode] = @CorSchlCode,[CorTypeCode] = @CorTypeCode,[Zip_Code] = @Zip_Code,[RegistedCode] = @RegistedCode,[PoliticalCode] = @PoliticalCode,[Origin] = @Origin,[OverSea] = @OverSea,[GrdSchool] = @GrdSchool,[StdAddress] = @StdAddress,[Email] = @Email,[Des] = @Des,[RegisteDate] = @RegisteDate WHERE  [Id]=@Id";

		private static readonly string SqlDeleteStudent = "DELETE [Student] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllStudent = "SELECT [Id], [Code],[StdName],[UsedName],[StdState],[IndentifyType],[StdCate],[IndentifyCode],[Gender],[AdmissDate],[ProfessCode],[ResidenceName],[ResidenceType],[Nation],[Birthday],[AdmissTypeCode],[Phone],[CorSchlCode],[CorTypeCode],[Zip_Code],[RegistedCode],[PoliticalCode],[Origin],[OverSea],[GrdSchool],[StdAddress],[Email],[Des],[RegisteDate] FROM [Student]";
		#endregion

		#region IStudentProvider Members
		public Core.Business.Student Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectStudent);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.Student student = new Core.Business.Student();

				if (!reader.IsDBNull(0)) student.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) student.Code = reader.GetString(1);
				if (!reader.IsDBNull(2)) student.StdName = reader.GetString(2);
				if (!reader.IsDBNull(3)) student.UsedName = reader.GetString(3);
				if (!reader.IsDBNull(4)) student.StdState = reader.GetString(4);
				if (!reader.IsDBNull(5)) student.IndentifyType = reader.GetString(5);
				if (!reader.IsDBNull(6)) student.StdCate = reader.GetString(6);
				if (!reader.IsDBNull(7)) student.IndentifyCode = reader.GetString(7);
				if (!reader.IsDBNull(8)) student.Gender = reader.GetInt32(8);
				if (!reader.IsDBNull(9)) student.AdmissDate = reader.GetDateTime(9);
				if (!reader.IsDBNull(10)) student.ProfessCode = reader.GetString(10);
				if (!reader.IsDBNull(11)) student.ResidenceName = reader.GetString(11);
				if (!reader.IsDBNull(12)) student.ResidenceType = reader.GetString(12);
				if (!reader.IsDBNull(13)) student.Nation = reader.GetString(13);
				if (!reader.IsDBNull(14)) student.Birthday = reader.GetDateTime(14);
				if (!reader.IsDBNull(15)) student.AdmissTypeCode = reader.GetString(15);
				if (!reader.IsDBNull(16)) student.Phone = reader.GetString(16);
				if (!reader.IsDBNull(17)) student.CorSchlCode = reader.GetString(17);
				if (!reader.IsDBNull(18)) student.CorTypeCode = reader.GetString(18);
				if (!reader.IsDBNull(19)) student.Zip_Code = reader.GetString(19);
				if (!reader.IsDBNull(20)) student.RegistedCode = reader.GetString(20);
				if (!reader.IsDBNull(21)) student.PoliticalCode = reader.GetString(21);
				if (!reader.IsDBNull(22)) student.Origin = reader.GetString(22);
				if (!reader.IsDBNull(23)) student.OverSea = reader.GetBoolean(23);
				if (!reader.IsDBNull(24)) student.GrdSchool = reader.GetString(24);
				if (!reader.IsDBNull(25)) student.StdAddress = reader.GetString(25);
				if (!reader.IsDBNull(26)) student.Email = reader.GetString(26);
				if (!reader.IsDBNull(27)) student.Des = reader.GetString(27);
				if (!reader.IsDBNull(28)) student.RegisteDate = reader.GetDateTime(28);

				return student;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.Student student)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Code", SqlDbType.NVarChar, student.Code);
			sql.AddParameter("@StdName", SqlDbType.NVarChar, student.StdName);
			sql.AddParameter("@UsedName", SqlDbType.NVarChar, student.UsedName);
			sql.AddParameter("@StdState", SqlDbType.NVarChar, student.StdState);
			sql.AddParameter("@IndentifyType", SqlDbType.NVarChar, student.IndentifyType);
			sql.AddParameter("@StdCate", SqlDbType.NVarChar, student.StdCate);
			sql.AddParameter("@IndentifyCode", SqlDbType.NVarChar, student.IndentifyCode);
			sql.AddParameter("@Gender", SqlDbType.Int, student.Gender);
			sql.AddParameter("@AdmissDate", SqlDbType.DateTime, student.AdmissDate);
			sql.AddParameter("@ProfessCode", SqlDbType.NVarChar, student.ProfessCode);
			sql.AddParameter("@ResidenceName", SqlDbType.NVarChar, student.ResidenceName);
			sql.AddParameter("@ResidenceType", SqlDbType.NVarChar, student.ResidenceType);
			sql.AddParameter("@Nation", SqlDbType.NVarChar, student.Nation);
			sql.AddParameter("@Birthday", SqlDbType.DateTime, student.Birthday);
			sql.AddParameter("@AdmissTypeCode", SqlDbType.NVarChar, student.AdmissTypeCode);
			sql.AddParameter("@Phone", SqlDbType.NVarChar, student.Phone);
			sql.AddParameter("@CorSchlCode", SqlDbType.NVarChar, student.CorSchlCode);
			sql.AddParameter("@CorTypeCode", SqlDbType.NVarChar, student.CorTypeCode);
			sql.AddParameter("@Zip_Code", SqlDbType.NVarChar, student.Zip_Code);
			sql.AddParameter("@RegistedCode", SqlDbType.NVarChar, student.RegistedCode);
			sql.AddParameter("@PoliticalCode", SqlDbType.NVarChar, student.PoliticalCode);
			sql.AddParameter("@Origin", SqlDbType.NVarChar, student.Origin);
			sql.AddParameter("@OverSea", SqlDbType.Bit, student.OverSea);
			sql.AddParameter("@GrdSchool", SqlDbType.NVarChar, student.GrdSchool);
			sql.AddParameter("@StdAddress", SqlDbType.NVarChar, student.StdAddress);
			sql.AddParameter("@Email", SqlDbType.NVarChar, student.Email);
			sql.AddParameter("@Des", SqlDbType.NVarChar, student.Des);
			sql.AddParameter("@RegisteDate", SqlDbType.DateTime, student.RegisteDate);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertStudent);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) student.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.Student student)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Code", SqlDbType.NVarChar, student.Code);
			sql.AddParameter("@StdName", SqlDbType.NVarChar, student.StdName);
			sql.AddParameter("@UsedName", SqlDbType.NVarChar, student.UsedName);
			sql.AddParameter("@StdState", SqlDbType.NVarChar, student.StdState);
			sql.AddParameter("@IndentifyType", SqlDbType.NVarChar, student.IndentifyType);
			sql.AddParameter("@StdCate", SqlDbType.NVarChar, student.StdCate);
			sql.AddParameter("@IndentifyCode", SqlDbType.NVarChar, student.IndentifyCode);
			sql.AddParameter("@Gender", SqlDbType.Int, student.Gender);
			sql.AddParameter("@AdmissDate", SqlDbType.DateTime, student.AdmissDate);
			sql.AddParameter("@ProfessCode", SqlDbType.NVarChar, student.ProfessCode);
			sql.AddParameter("@ResidenceName", SqlDbType.NVarChar, student.ResidenceName);
			sql.AddParameter("@ResidenceType", SqlDbType.NVarChar, student.ResidenceType);
			sql.AddParameter("@Nation", SqlDbType.NVarChar, student.Nation);
			sql.AddParameter("@Birthday", SqlDbType.DateTime, student.Birthday);
			sql.AddParameter("@AdmissTypeCode", SqlDbType.NVarChar, student.AdmissTypeCode);
			sql.AddParameter("@Phone", SqlDbType.NVarChar, student.Phone);
			sql.AddParameter("@CorSchlCode", SqlDbType.NVarChar, student.CorSchlCode);
			sql.AddParameter("@CorTypeCode", SqlDbType.NVarChar, student.CorTypeCode);
			sql.AddParameter("@Zip_Code", SqlDbType.NVarChar, student.Zip_Code);
			sql.AddParameter("@RegistedCode", SqlDbType.NVarChar, student.RegistedCode);
			sql.AddParameter("@PoliticalCode", SqlDbType.NVarChar, student.PoliticalCode);
			sql.AddParameter("@Origin", SqlDbType.NVarChar, student.Origin);
			sql.AddParameter("@OverSea", SqlDbType.Bit, student.OverSea);
			sql.AddParameter("@GrdSchool", SqlDbType.NVarChar, student.GrdSchool);
			sql.AddParameter("@StdAddress", SqlDbType.NVarChar, student.StdAddress);
			sql.AddParameter("@Email", SqlDbType.NVarChar, student.Email);
			sql.AddParameter("@Des", SqlDbType.NVarChar, student.Des);
			sql.AddParameter("@RegisteDate", SqlDbType.DateTime, student.RegisteDate);
			sql.AddParameter("@Id", SqlDbType.Int, student.Id);
			sql.ExecuteSql(SqlUpdateStudent);
		}

		public void Delete(Core.Business.Student student)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, student.Id);
			sql.ExecuteSql(SqlDeleteStudent);
		}

		public IList<Core.Business.Student> GetAllStudent()
		{
			IList<Core.Business.Student> studentlist = new List<Core.Business.Student>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllStudent);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.Student student = new Core.Business.Student();

					if (!reader.IsDBNull(0)) student.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) student.Code = reader.GetString(1);
					if (!reader.IsDBNull(2)) student.StdName = reader.GetString(2);
					if (!reader.IsDBNull(3)) student.UsedName = reader.GetString(3);
					if (!reader.IsDBNull(4)) student.StdState = reader.GetString(4);
					if (!reader.IsDBNull(5)) student.IndentifyType = reader.GetString(5);
					if (!reader.IsDBNull(6)) student.StdCate = reader.GetString(6);
					if (!reader.IsDBNull(7)) student.IndentifyCode = reader.GetString(7);
					if (!reader.IsDBNull(8)) student.Gender = reader.GetInt32(8);
					if (!reader.IsDBNull(9)) student.AdmissDate = reader.GetDateTime(9);
					if (!reader.IsDBNull(10)) student.ProfessCode = reader.GetString(10);
					if (!reader.IsDBNull(11)) student.ResidenceName = reader.GetString(11);
					if (!reader.IsDBNull(12)) student.ResidenceType = reader.GetString(12);
					if (!reader.IsDBNull(13)) student.Nation = reader.GetString(13);
					if (!reader.IsDBNull(14)) student.Birthday = reader.GetDateTime(14);
					if (!reader.IsDBNull(15)) student.AdmissTypeCode = reader.GetString(15);
					if (!reader.IsDBNull(16)) student.Phone = reader.GetString(16);
					if (!reader.IsDBNull(17)) student.CorSchlCode = reader.GetString(17);
					if (!reader.IsDBNull(18)) student.CorTypeCode = reader.GetString(18);
					if (!reader.IsDBNull(19)) student.Zip_Code = reader.GetString(19);
					if (!reader.IsDBNull(20)) student.RegistedCode = reader.GetString(20);
					if (!reader.IsDBNull(21)) student.PoliticalCode = reader.GetString(21);
					if (!reader.IsDBNull(22)) student.Origin = reader.GetString(22);
					if (!reader.IsDBNull(23)) student.OverSea = reader.GetBoolean(23);
					if (!reader.IsDBNull(24)) student.GrdSchool = reader.GetString(24);
					if (!reader.IsDBNull(25)) student.StdAddress = reader.GetString(25);
					if (!reader.IsDBNull(26)) student.Email = reader.GetString(26);
					if (!reader.IsDBNull(27)) student.Des = reader.GetString(27);
					if (!reader.IsDBNull(28)) student.RegisteDate = reader.GetDateTime(28);

					student.MarkOld();
					studentlist.Add(student);
				}
				reader.Close();
			}
			return studentlist;
		}
		#endregion
	}
}
